/*
 * Copyright (c) 2006 Sun Microsystems, Inc.  All rights reserved.  U.S.
 * Government Rights - Commercial software.  Government users are subject
 * to the Sun Microsystems, Inc. standard license agreement and
 * applicable provisions of the FAR and its supplements.  Use is subject
 * to license terms.
 *
 * This distribution may include materials developed by third parties.
 * Sun, Sun Microsystems, the Sun logo, Java and J2EE are trademarks
 * or registered trademarks of Sun Microsystems, Inc. in the U.S. and
 * other countries.
 *
 * Copyright (c) 2006 Sun Microsystems, Inc. Tous droits reserves.
 *
 * Droits du gouvernement americain, utilisateurs gouvernementaux - logiciel
 * commercial. Les utilisateurs gouvernementaux sont soumis au contrat de
 * licence standard de Sun Microsystems, Inc., ainsi qu'aux dispositions
 * en vigueur de la FAR (Federal Acquisition Regulations) et des
 * supplements a celles-ci.  Distribue par des licences qui en
 * restreignent l'utilisation.
 *
 * Cette distribution peut comprendre des composants developpes par des
 * tierces parties. Sun, Sun Microsystems, le logo Sun, Java et J2EE
 * sont des marques de fabrique ou des marques deposees de Sun
 * Microsystems, Inc. aux Etats-Unis et dans d'autres pays.
 */


package dao;

import java.sql.*;
import model.Item;
import model.Item.Category;
import java.util.*;

public class ItemDBAO extends BaseDBAO{
    private ArrayList<Item> items;
    Connection con;
    
    public ItemDBAO() throws Exception{
    	super();
    }
        
    public ArrayList<Item> getSingleItems() throws Exception{
    	
        items = new ArrayList<Item>();
        
        try {
            String selectStatement = "select * " + "from items";
            con = getConnection();
            
            PreparedStatement prepStmt = con.prepareStatement(selectStatement);
            ResultSet rs = prepStmt.executeQuery();
            
            while (rs.next()) {
            	
            	Item itemfromDB = new Item();
            	itemfromDB.setItemNumber( rs.getInt(1) );
            	itemfromDB.setName( rs.getString(2) );
            	itemfromDB.setPrice( rs.getDouble(3) );
            	itemfromDB.setDescription(rs.getString(4));
            	
            	if( rs.getString(5).equals("food") ){
            		itemfromDB.setCategory(Category.FOOD);
            	}
            	else{ //rs.getString(5).equals("drink")
            		itemfromDB.setCategory(Category.DRINK);
            	}
            	
            	itemfromDB.setImagePath( rs.getString(6) );
                
                items.add(itemfromDB);
            }
            
            prepStmt.close();
        } catch (SQLException ex) {
        	throw new Exception();
        }
        
        releaseConnection();
        Collections.sort(items);
        
        return items;
    }
    
    public Item getItemByItemNumber(int itemNumber) throws Exception {
    	
        try {
            String selectStatement = "select * " + "from items where itemNumber = ? ";
            con = getConnection();
            
            PreparedStatement prepStmt = con.prepareStatement(selectStatement);
            prepStmt.setInt(1, itemNumber);
            ResultSet rs = prepStmt.executeQuery();
            
            if (rs.next()) {
            	
            	Item itemfromDB = new Item();
            	itemfromDB.setItemNumber( rs.getInt(1) );
            	itemfromDB.setName( rs.getString(2) );
            	itemfromDB.setPrice( rs.getDouble(3) );
            	itemfromDB.setDescription(rs.getString(4));
            	
            	if( rs.getString(5).equals("food") ){
            		itemfromDB.setCategory(Category.FOOD);
            	}
            	else{ //rs.getString(5).equals("drink")
            		itemfromDB.setCategory(Category.DRINK);
            	}
            	
            	itemfromDB.setImagePath( rs.getString(6) );
                
                prepStmt.close();
                releaseConnection();
                
                return itemfromDB;
            } else {
                prepStmt.close();
                releaseConnection();
                throw new Exception("Couldn't find item: " + itemNumber);
            }
        } catch (SQLException ex) {
            releaseConnection();
            throw new Exception("Couldn't find item: " + itemNumber + " " + ex.getMessage());
        }
    }
}
